using System;
using System.Data;
using System.Data.SqlClient;
using Framework.Library;
using System.Web.UI.WebControls;
using Framework.Library.Utilities;

namespace Framework.Module.UpDownLoad.Library
{
    public class Categories_Controller
    {
        public static DataTable GetAll()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Share_Categories_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public static DataTable GetAllByParentID(int ParentID)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Share_Categories_GetAllByParentID", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("ParentID", ParentID);
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static void Delete(int _iD)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Share_Categories_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _iD);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static int Insert(Categories_Info _share_CategorieInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Share_Categories_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@NameENG", _share_CategorieInfo.NameENG);
            dbCmd.Parameters.AddWithValue("@NameVIE", _share_CategorieInfo.NameVIE);
            dbCmd.Parameters.AddWithValue("@ParentID", _share_CategorieInfo.ParentID);
            dbCmd.Parameters.AddWithValue("@CatOrder", _share_CategorieInfo.CatOrder);
            dbCmd.Parameters.AddWithValue("@CatVisible", _share_CategorieInfo.CatVisible);
            dbCmd.Parameters.AddWithValue("@CatEnable", _share_CategorieInfo.CatEnable);
            dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static void Update(Categories_Info _share_CategorieInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Share_Categories_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _share_CategorieInfo.ID);
            dbCmd.Parameters.AddWithValue("@NameENG", _share_CategorieInfo.NameENG);
            dbCmd.Parameters.AddWithValue("@NameVIE", _share_CategorieInfo.NameVIE);
            dbCmd.Parameters.AddWithValue("@ParentID", _share_CategorieInfo.ParentID);
            dbCmd.Parameters.AddWithValue("@CatOrder", _share_CategorieInfo.CatOrder);
            dbCmd.Parameters.AddWithValue("@CatVisible", _share_CategorieInfo.CatVisible);
            dbCmd.Parameters.AddWithValue("@CatEnable", _share_CategorieInfo.CatEnable);            
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static Categories_Info GetInfo(int _iD)
        {
            Categories_Info retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Share_Categories_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _iD);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new Categories_Info();
                    retVal.ID = Convert.ToInt32(dr["ID"]);
                    retVal.NameENG = Convert.ToString(dr["NameENG"]);
                    retVal.NameVIE = Convert.ToString(dr["NameVIE"]);
                    retVal.ParentID = Convert.ToInt32(dr["ParentID"]);
                    retVal.CatOrder = Convert.ToInt32(dr["CatOrder"]);
                    retVal.CatVisible = Convert.ToBoolean(dr["CatVisible"]);
                    retVal.CatEnable = Convert.ToBoolean(dr["CatEnable"]);                    
                }
                if (dr != null) dr.Close();
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static void FillToListBox(ListItemCollection lstItems, DataTable source, int rootId)
		{
            DataRow[] drCommands = source.Select("ParentID = " + rootId);
			foreach(DataRow row in drCommands)
			{
                if (AppEnv.GetLanguage() != "vi-VN")
                {
                    ListItem rootItem = new ListItem(row["NameENG"].ToString(), row["ID"].ToString());
                    rootItem.Attributes.Add("Level", "0");
                    lstItems.Add(rootItem);
                    LoadForCurListItem(lstItems, rootItem, source);
                }
                else
                {
                    ListItem rootItem = new ListItem(row["NameVIE"].ToString(), row["ID"].ToString());
                    rootItem.Attributes.Add("Level", "0");
                    lstItems.Add(rootItem);
                    LoadForCurListItem(lstItems, rootItem, source);
                }
				
				
			}
			lstItems.Insert(0, new ListItem("Root","0"));
		}

        private static void LoadForCurListItem(ListItemCollection lstItems, ListItem curItem, DataTable source)
        {

            int curCommandId = ConvertUtility.ToInt32(curItem.Value);
            int level = ConvertUtility.ToInt32(curItem.Attributes["Level"]);
            level++;
            DataRow[] drChildCommands = source.Select("ParentID = " + curCommandId);
            foreach (DataRow row in drChildCommands)
            {
                if (AppEnv.GetLanguage() != "vi-VN")
                {
                    ListItem item = new ListItem(MiscUtility.StringIndent(level) + row["NameENG"].ToString(), row["ID"].ToString());
                    item.Attributes.Add("Level", level.ToString());
                    lstItems.Add(item);
                    LoadForCurListItem(lstItems, item, source);
                }
                else
                {
                    ListItem item = new ListItem(MiscUtility.StringIndent(level) + row["NameVIE"].ToString(), row["ID"].ToString());
                    item.Attributes.Add("Level", level.ToString());
                    lstItems.Add(item);
                    LoadForCurListItem(lstItems, item, source);
                }
                
            }
        }
    }
}
